﻿using Dapper;
using MediatR;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using S.ReportForms.Domain.Dto;
using S.ReportForms.Read.API.Application.Command;
using System.Diagnostics.Metrics;
using System.Text;
using static S.ReportForms.Read.API.Application.Command.LambingStatisticsCommand;

namespace S.ReportForms.Read.API.Application.CommandHandlers
{
    public class LambingStatisticsCommandHandler : IRequestHandler<MonthLambingStatistics, List<List<int>>>, IRequestHandler<ParityLambingStatistics, List<string>>, IRequestHandler<ParityStateLambingStatistics, List<List<int>>>, IRequestHandler<LambingLactationStatistics, List<LambingLactationStatisticsDto>>,IRequestHandler<CountLambingStatices,List<LambingRursingDto>>
    {
        private readonly IConfiguration configuration;

        public LambingStatisticsCommandHandler(IConfiguration configuration)
        {
            this.configuration = configuration;
        }
        /// <summary>
        /// 月产羔统计
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async Task<List<List<int>>> Handle(MonthLambingStatistics request, CancellationToken cancellationToken)
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQueryFirst = new StringBuilder();
                    sqlQueryFirst.Append($@"
                SELECT
                    STRING_AGG(COALESCE(Count, '0'), ',') AS Result
                FROM (
                    SELECT
                        MONTH(BirthDate) AS Month,
                        COUNT(*) AS Count
                    FROM
                        LambRegistrationModel
                    WHERE
                        YEAR(BirthDate) = @BirthDate
                        AND BirthState = 1
                    GROUP BY
                        MONTH(BirthDate)
                ) AS t
                RIGHT JOIN (
                    SELECT 1 AS Month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
                    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
                    UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
                ) AS m ON t.Month = m.Month;
            ");

                    StringBuilder sqlQuerySecond = new StringBuilder();
                    sqlQuerySecond.Append($@"
                SELECT
                    STRING_AGG(COALESCE(Count, '0'), ',') AS Result
                FROM (
                    SELECT
                        MONTH(BirthDate) AS Month,
                        COUNT(*) AS Count
                    FROM
                        LambRegistrationModel
                    WHERE
                        YEAR(BirthDate) = @BirthDate
                        AND BirthState = 2
                    GROUP BY
                        MONTH(BirthDate)
                ) AS t
                RIGHT JOIN (
                    SELECT 1 AS Month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
                    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
                    UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
                ) AS m ON t.Month = m.Month;
            ");
                    StringBuilder sqlQueryThree = new StringBuilder();
                    sqlQueryThree.Append($@"
                SELECT
                    STRING_AGG(COALESCE(Count, '0'), ',') AS Result
                FROM (
                    SELECT
                        MONTH(BirthDate) AS Month,
                        COUNT(*) AS Count
                    FROM
                        LambRegistrationModel
                    WHERE
                        YEAR(BirthDate) = @BirthDate
                        AND BirthState = 3
                    GROUP BY
                        MONTH(BirthDate)
                ) AS t
                RIGHT JOIN (
                    SELECT 1 AS Month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
                    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
                    UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
                ) AS m ON t.Month = m.Month;
            ");
                    StringBuilder sqlQueryFour = new StringBuilder();
                    sqlQueryFour.Append($@"
                            SELECT
                                STRING_AGG(COALESCE(Count, '0'), ',') AS Result
                            FROM (
                                SELECT
                                    MONTH(BirthDate) AS Month,
                                    COUNT(*) AS Count
                                FROM
                                    LambRegistrationModel
                                WHERE
                                    YEAR(BirthDate) = @BirthDate
                                    AND BirthState = 4
                                GROUP BY
                                    MONTH(BirthDate)
                            ) AS t
                            RIGHT JOIN (
                                SELECT 1 AS Month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
                                UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
                                UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
                                 ) AS m ON t.Month = m.Month;
            ");
                    StringBuilder sqlQueryFive = new StringBuilder();
                    sqlQueryFive.Append($@"
                                    SELECT
                                        STRING_AGG(COALESCE(Count, '0'), ',') AS Result
                                    FROM (
                                        SELECT
                                            MONTH(BirthDate) AS Month,
                                            COUNT(*) AS Count
                                        FROM
                                            LambRegistrationModel
                                        WHERE
                                            YEAR(BirthDate) = @BirthDate
                                            AND BirthState = 5
                                        GROUP BY
                                            MONTH(BirthDate)
                                    ) AS t
                                    RIGHT JOIN (
                                        SELECT 1 AS Month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
                                        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
                                        UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
                                    ) AS m ON t.Month = m.Month;
            ");
                    StringBuilder sqlQuerySix = new StringBuilder();
                    sqlQuerySix.Append($@"
                                    SELECT
                                        STRING_AGG(COALESCE(Count, '0'), ',') AS Result
                                    FROM (
                                        SELECT
                                            MONTH(BirthDate) AS Month,
                                            COUNT(*) AS Count
                                        FROM
                                            LambRegistrationModel
                                        WHERE
                                            YEAR(BirthDate) = @BirthDate
                                            AND BirthState = 6
                                        GROUP BY
                                            MONTH(BirthDate)
                                    ) AS t
                                    RIGHT JOIN (
                                        SELECT 1 AS Month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
                                        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
                                        UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
                                    ) AS m ON t.Month = m.Month;
            ");
                    StringBuilder sqlQueryCount = new StringBuilder();
                    sqlQueryCount.Append($@"
                                            SELECT
                                COALESCE(STRING_AGG(CAST(ISNULL(t.Count, 0) AS NVARCHAR), ','), '0') AS Result
                            FROM (
                                SELECT
                                    MONTH(BirthDate) AS Month,
                                    COUNT(*) AS Count
                                FROM
                                    LambRegistrationModel
                                WHERE
                                    YEAR(BirthDate) = @BirthDate
                                GROUP BY
                                    MONTH(BirthDate)
                            ) AS t
                            RIGHT JOIN (
                                SELECT 1 AS Month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
                                UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
                                UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
                            ) AS m ON t.Month = m.Month;
            ");

                    List<List<int>> result = new List<List<int>>();

                    string FirstData = await connection.QuerySingleOrDefaultAsync<string>(sqlQueryFirst.ToString(), new { BirthDate = request.Year });
                    List<int> healthyNumbers = FirstData.Split(',').Select(int.Parse).ToList();
                    result.Add(healthyNumbers);

                    string SecondData = await connection.QuerySingleOrDefaultAsync<string>(sqlQuerySecond.ToString(), new { BirthDate = request.Year });
                    List<int> weakNumbers = SecondData.Split(',').Select(int.Parse).ToList();
                    result.Add(weakNumbers);

                    string ThreeData = await connection.QuerySingleOrDefaultAsync<string>(sqlQueryThree.ToString(), new { BirthDate = request.Year });
                    List<int> ThreeNumbers = ThreeData.Split(',').Select(int.Parse).ToList();
                    result.Add(ThreeNumbers);

                    string FourData = await connection.QuerySingleOrDefaultAsync<string>(sqlQueryFour.ToString(), new { BirthDate = request.Year });
                    List<int> FourNumbers = FourData.Split(',').Select(int.Parse).ToList();
                    result.Add(FourNumbers);

                    string FiveData = await connection.QuerySingleOrDefaultAsync<string>(sqlQueryFive.ToString(), new { BirthDate = request.Year });
                    List<int> FiveNumbers = FiveData.Split(',').Select(int.Parse).ToList();
                    result.Add(FiveNumbers);

                    string SixData = await connection.QuerySingleOrDefaultAsync<string>(sqlQuerySix.ToString(), new { BirthDate = request.Year });
                    List<int> SixNumbers = SixData.Split(',').Select(int.Parse).ToList();
                    result.Add(SixNumbers);
                    string CountData = await connection.QuerySingleOrDefaultAsync<string>(sqlQueryCount.ToString(), new { BirthDate = request.Year });
                    List<int> CountNumbers = CountData.Split(',').Select(int.Parse).ToList();
                    result.Add(CountNumbers);

                    return result;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 胎次动态统计
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<List<string>> Handle(ParityLambingStatistics request, CancellationToken cancellationToken)
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    var sqlQuery = @"
                SELECT '胎次' + CONVERT(VARCHAR(10), CurrentPregnancy) AS '胎次'
                FROM BreedingHistoryModel
                GROUP BY CurrentPregnancy;
            ";

                    var result = await connection.QueryAsync<string>(sqlQuery);
                    return result.ToList();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 胎次产羔状态统计
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<List<List<int>>> Handle(ParityStateLambingStatistics request, CancellationToken cancellationToken)
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQueryFirst = new StringBuilder();
                    sqlQueryFirst.Append($@"
                                        SELECT STUFF((
                                        SELECT ',' + CONVERT(VARCHAR(10), SUM(HealthyNumber))
                                        FROM BreedingHistoryModel
                                        GROUP BY CurrentPregnancy
                                        FOR XML PATH('')), 1, 1, '') AS '健羔数';
                                        ");

                    StringBuilder sqlQuerySecond = new StringBuilder();
                    sqlQuerySecond.Append($@"
                                        SELECT STUFF((
                                        SELECT ',' + CONVERT(VARCHAR(10), SUM(LambNumber))
                                        FROM BreedingHistoryModel
                                        GROUP BY CurrentPregnancy
                                        FOR XML PATH('')), 1, 1, '') AS '弱羔数';
                                        ");
                    StringBuilder sqlQueryThree = new StringBuilder();
                    sqlQueryThree.Append($@"
                                        SELECT STUFF((
                                        SELECT ',' + CONVERT(VARCHAR(10), SUM(DeformityNumber))
                                        FROM BreedingHistoryModel
                                        GROUP BY CurrentPregnancy
                                        FOR XML PATH('')), 1, 1, '') AS '畸形数';
                                        ");
                    StringBuilder sqlQueryFour = new StringBuilder();
                    sqlQueryFour.Append($@"
                                        SELECT STUFF((
                                        SELECT ',' + CONVERT(VARCHAR(10), SUM(StillbirthsNumber))
                                        FROM BreedingHistoryModel
                                        GROUP BY CurrentPregnancy
                                        FOR XML PATH('')), 1, 1, '') AS '死胎数';
                                        ");
                    StringBuilder sqlQueryFive = new StringBuilder();
                    sqlQueryFive.Append($@"
                                        SELECT STUFF((
                                        SELECT ',' + CONVERT(VARCHAR(10), SUM(MummyNumber))
                                        FROM BreedingHistoryModel
                                        GROUP BY CurrentPregnancy
                                        FOR XML PATH('')), 1, 1, '') AS '木乃伊数';
                                        ");
                    StringBuilder sqlQuerySix = new StringBuilder();
                    sqlQuerySix.Append($@"
                                        SELECT STUFF((
                                        SELECT ',' + CONVERT(VARCHAR(10), SUM(FreaksNumber))
                                        FROM BreedingHistoryModel
                                        GROUP BY CurrentPregnancy
                                        FOR XML PATH('')), 1, 1, '') AS '畸胎数';
                                        ");
                    //
                    StringBuilder sqlQueryHy = new StringBuilder();
                    sqlQuerySix.Append($@"
                                    SELECT COUNT(*) AS total_count
                                    FROM BreedingHistoryModel
                                    GROUP BY CurrentPregnancy;
                                                ");

                    List<List<int>> result = new List<List<int>>();

                    string FirstData = await connection.QuerySingleOrDefaultAsync<string>(sqlQueryFirst.ToString());
                    List<int> healthyNumbers = FirstData.Split(',').Select(int.Parse).ToList();
                    result.Add(healthyNumbers);

                    string SecondData = await connection.QuerySingleOrDefaultAsync<string>(sqlQuerySecond.ToString());
                    List<int> weakNumbers = SecondData.Split(',').Select(int.Parse).ToList();
                    result.Add(weakNumbers);

                    string ThreeData = await connection.QuerySingleOrDefaultAsync<string>(sqlQueryThree.ToString());
                    List<int> ThreeNumbers = ThreeData.Split(',').Select(int.Parse).ToList();
                    result.Add(ThreeNumbers);

                    string FourData = await connection.QuerySingleOrDefaultAsync<string>(sqlQueryFour.ToString());
                    List<int> FourNumbers = FourData.Split(',').Select(int.Parse).ToList();
                    result.Add(FourNumbers);

                    string FiveData = await connection.QuerySingleOrDefaultAsync<string>(sqlQueryFive.ToString());
                    List<int> FiveNumbers = FiveData.Split(',').Select(int.Parse).ToList();
                    result.Add(FiveNumbers);

                    string SixData = await connection.QuerySingleOrDefaultAsync<string>(sqlQuerySix.ToString());
                    List<int> SixNumbers = SixData.Split(',').Select(int.Parse).ToList();
                    result.Add(SixNumbers);

                    return result;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }



        /// <summary>
        /// 产羔哺乳情况
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<List<LambingLactationStatisticsDto>> Handle(LambingLactationStatistics request, CancellationToken cancellationToken)
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQuery = new StringBuilder();

                    //分娩窝数
                    if (request.Indicators == 1)
                    {
                        sqlQuery.Append($@"SELECT MONTH(DeliveryDate) AS Month, COUNT(*) AS TotalCount
                            FROM BreedingHistoryModel 
                            WHERE YEAR(DeliveryDate) = {request.SelectYear}
                            GROUP BY MONTH(DeliveryDate)
                            ORDER BY MONTH(DeliveryDate);");
                    }

                    //产羔数
                    if (request.Indicators == 2)
                    {
                        sqlQuery.Append($@"SELECT
                            MONTH(DeliveryDate) AS Month,
                            SUM(HealthyNumber + LambNumber + DeformityNumber + StillbirthsNumber + MummyNumber + FreaksNumber) AS TotalCount
                            FROM BreedingHistoryModel
                            WHERE YEAR(DeliveryDate) =  {request.SelectYear}
                            GROUP BY MONTH(DeliveryDate)
                            ORDER BY MONTH(DeliveryDate);");
                    }
                    //健羔数
                    if (request.Indicators == 3)
                    {
                        sqlQuery.Append($@"SELECT MONTH(DeliveryDate) AS Month,
                          ROUND((SUM(HealthyNumber) * 100.0 / SUM(HealthyNumber + LambNumber + DeformityNumber + StillbirthsNumber + MummyNumber + FreaksNumber)/100), 2) AS TotalCount
                        FROM BreedingHistoryModel
                        WHERE YEAR(DeliveryDate) =  {request.SelectYear}
                        GROUP BY MONTH(DeliveryDate)
                        ORDER BY MONTH(DeliveryDate);");
                    }

                    //弱羔数
                    if (request.Indicators == 4)
                    {
                        sqlQuery.Append($@"SELECT MONTH(DeliveryDate) AS Month,
                          ROUND((SUM(LambNumber) * 100.0 / SUM(HealthyNumber + LambNumber + DeformityNumber + StillbirthsNumber + MummyNumber + FreaksNumber)/100), 2) AS TotalCount
                        FROM BreedingHistoryModel
                        WHERE YEAR(DeliveryDate) =  {request.SelectYear}
                        GROUP BY MONTH(DeliveryDate)
                        ORDER BY MONTH(DeliveryDate);");
                    }
                    //畸形数
                    if (request.Indicators == 5)
                    {
                        sqlQuery.Append($@"SELECT MONTH(DeliveryDate) AS Month,
                          ROUND((SUM(DeformityNumber) * 100.0 / SUM(HealthyNumber + LambNumber + DeformityNumber + StillbirthsNumber + MummyNumber + FreaksNumber)/100), 2) AS TotalCount
                        FROM BreedingHistoryModel
                        WHERE YEAR(DeliveryDate) = {request.SelectYear}
                        GROUP BY MONTH(DeliveryDate)
                        ORDER BY MONTH(DeliveryDate);");
                    }
                    //死胎数
                    if (request.Indicators == 6)
                    {
                        sqlQuery.Append($@"SELECT MONTH(DeliveryDate) AS Month,
                          ROUND((SUM(StillbirthsNumber) * 100.0 / SUM(HealthyNumber + LambNumber + DeformityNumber + StillbirthsNumber + MummyNumber + FreaksNumber)/100), 2) AS TotalCount
                        FROM BreedingHistoryModel
                        WHERE YEAR(DeliveryDate) = {request.SelectYear}
                        GROUP BY MONTH(DeliveryDate)
                        ORDER BY MONTH(DeliveryDate);");
                    }
                    //木乃伊数
                    if (request.Indicators == 7)
                    {
                        sqlQuery.Append($@"SELECT MONTH(DeliveryDate) AS Month,
                          ROUND((SUM(MummyNumber) * 100.0 / SUM(HealthyNumber + LambNumber + DeformityNumber + StillbirthsNumber + MummyNumber + FreaksNumber)/100), 2) AS TotalCount
                        FROM BreedingHistoryModel
                        WHERE YEAR(DeliveryDate) = {request.SelectYear}
                        GROUP BY MONTH(DeliveryDate)
                        ORDER BY MONTH(DeliveryDate);");
                    }

                    //畸胎数
                    if (request.Indicators == 8)
                    {
                        sqlQuery.Append($@"SELECT MONTH(DeliveryDate) AS Month,
                          ROUND((SUM(FreaksNumber) * 100.0 / SUM(HealthyNumber + LambNumber + DeformityNumber + StillbirthsNumber + MummyNumber + FreaksNumber)/100), 2) AS TotalCount
                        FROM BreedingHistoryModel
                        WHERE YEAR(DeliveryDate) = {request.SelectYear}
                        GROUP BY MONTH(DeliveryDate)
                        ORDER BY MONTH(DeliveryDate);");
                    }


                    var result = connection.Query<LambingLactationStatisticsDto>(sqlQuery.ToString()).ToList();
                    return result;
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        public async Task<List<LambingRursingDto>> Handle(CountLambingStatices request, CancellationToken cancellationToken)
        {
            string connectionString = configuration.GetValue<string>("ConnectionStrings");
            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    StringBuilder sqlQueryFirst = new StringBuilder();
                    sqlQueryFirst.Append($@"
                                        SELECT 
				                        T1.Year,
				                        T1.Month,
				                        T1.UnhealthyCount,
				                        T2.TotalCount,
				                        T2.TotalHealthyNumber,
				                        T2.TotalLambNumber,
				                        T2.TotalDeformityNumber,
				                        T2.TotalStillbirthsNumber,
				                        T2.TotalMummyNumber,
				                        T2.TotalFreaksNumber,
				                        (T2.TotalHealthyNumber + T2.TotalLambNumber + T2.TotalDeformityNumber +
				                         T2.TotalStillbirthsNumber + T2.TotalMummyNumber + T2.TotalFreaksNumber) AS TotalDeliveryNumber,
				                        T3.Count_150Days,
				                        T3.Count_LessThan145Days,
				                        T4.LambSurvivalRate,
				                        COUNT(CASE WHEN T5.HealthyNumber < 3 THEN 1 END) AS CountOfEwes,
				                        ROUND(CAST(COUNT(CASE WHEN T5.HealthyNumber < 3 THEN 1 END) AS FLOAT) / NULLIF(CAST(COUNT(T5.BDHRId) AS FLOAT), 0), 2) AS EwesRatio
		                        FROM
		                        (
				                        SELECT 
						                        YEAR(DeliveryDate) AS Year, 
						                        MONTH(DeliveryDate) AS Month, 
						                        SUM(CASE WHEN HealthyNumber < 1 THEN 1 ELSE 0 END) AS UnhealthyCount
				                        FROM 
						                        BreedingHistoryModel
				                        WHERE
						                        YEAR(DeliveryDate) = @Year  -- 使用变量进行筛选
				                        GROUP BY 
						                        YEAR(DeliveryDate), 
						                        MONTH(DeliveryDate)
		                        ) AS T1
		                        INNER JOIN
		                        (
				                        SELECT 
						                        YEAR(DeliveryDate) AS Year,
						                        MONTH(DeliveryDate) AS Month,
						                        COUNT(*) AS TotalCount,
						                        SUM(HealthyNumber) AS TotalHealthyNumber,
						                        SUM(LambNumber) AS TotalLambNumber,
						                        SUM(DeformityNumber) AS TotalDeformityNumber,
						                        SUM(StillbirthsNumber) AS TotalStillbirthsNumber,
						                        SUM(MummyNumber) AS TotalMummyNumber,
						                        SUM(FreaksNumber) AS TotalFreaksNumber
				                        FROM 
						                        BreedingHistoryModel
				                        WHERE
						                        YEAR(DeliveryDate) = @Year  -- 使用变量进行筛选
				                        GROUP BY 
						                        YEAR(DeliveryDate), 
						                        MONTH(DeliveryDate)
		                        ) AS T2
		                        ON T1.Year = T2.Year AND T1.Month = T2.Month
		                        INNER JOIN
		                        (
				                        SELECT 
						                        YEAR(DeliveryDate) AS Year,
						                        MONTH(DeliveryDate) AS Month,
						                        COUNT(*) AS Count_150Days,
						                        SUM(CASE WHEN DATEDIFF(DAY, BreedingDate, DeliveryDate) < 145 THEN 1 ELSE 0 END) AS Count_LessThan145Days
				                        FROM 
						                        BreedingHistoryModel
				                        WHERE
						                        YEAR(DeliveryDate) = @Year  -- 使用变量进行筛选
				                        GROUP BY 
						                        YEAR(DeliveryDate), 
						                        MONTH(DeliveryDate)
		                        ) AS T3
		                        ON T1.Year = T3.Year AND T1.Month = T3.Month
		                        INNER JOIN
		                        (
				                        SELECT 
						                        YEAR(DeliveryDate) AS Year,
						                        MONTH(DeliveryDate) AS Month,
						                        CAST(SUM(HealthyNumber) + SUM(LambNumber) + SUM(DeformityNumber) + SUM(FreaksNumber) AS FLOAT) / 
						                        CAST(SUM(HealthyNumber) + SUM(LambNumber) + SUM(DeformityNumber) + SUM(FreaksNumber) + SUM(StillbirthsNumber) + SUM(MummyNumber) AS FLOAT) AS LambSurvivalRate
				                        FROM 
						                        BreedingHistoryModel
				                        WHERE
						                        YEAR(DeliveryDate) = @Year  -- 使用变量进行筛选
				                        GROUP BY 
						                        YEAR(DeliveryDate), MONTH(DeliveryDate)
		                        ) AS T4
		                        ON T1.Year = T4.Year AND T1.Month = T4.Month
		                        LEFT JOIN BreedingHistoryModel AS T5
		                        ON T1.Year = YEAR(T5.DeliveryDate) AND T1.Month = MONTH(T5.DeliveryDate)
		                        WHERE
				                        T1.Year = @Year  -- 使用变量进行筛选
		                        GROUP BY 
				                        T1.Year, 
				                        T1.Month, 
				                        T1.UnhealthyCount, 
				                        T2.TotalCount, 
				                        T2.TotalHealthyNumber, 
				                        T2.TotalLambNumber, 
				                        T2.TotalDeformityNumber, 
				                        T2.TotalStillbirthsNumber, 
				                        T2.TotalMummyNumber, 
				                        T2.TotalFreaksNumber,
				                        T3.Count_150Days,
				                        T3.Count_LessThan145Days,
				                        T4.LambSurvivalRate
		                        ORDER BY 
				                        T1.Year, 
				                        T1.Month;

                                        ");
                    var result = await connection.QueryAsync<LambingRursingDto>(sqlQueryFirst.ToString(), new {@Year=request.Year });
                    return result.ToList();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}
